Skip to main content

SQL Overview

SQL，指结构化查询语言，全称是 Structured Query Language。

SQL 是用于访问和处理数据库的标准的计算机语言。

MySQL 是一个关系型数据库管理系统，MySQL 可以使用 SQL 语言来访问数据库。

SQL 可以：

  • 创建新的数据库、表、存储过程和视图。
  • 在数据库中插入、更新、删除记录。
  • 从数据库中检索数据等。

虽然 SQL 是一门 ANSI（美国国家标准化组织）标准的计算机语言，但是仍然存在着多种不同版本的 SQL 语言。除了 SQL 标准之外，大多数 SQL 数据库程序都有自己的专有扩展，但它们都支持主要命令。

Components of SQL#

  1. Data definition language — DDL
    • Create, alter, delete relations and their attributes (Later in the course)
  2. Data manipulation Language — DML
    • Query one or more relations/tables
    • Insert, delete, modify tuples in relations
  3. Authorization Language — grant privileges to users

Execution of a SQL#

Execution of a SQL query is based on relational algebra

Example banner

Using SQL#

Stand-alone (CLI or GUI): SQL Plus (see tutorial)  Embedded in a host language (C, C++, Java, etc.)

work

Concepts: Database#

A collection of data that is logically coherent.

An organised, machine-readable collection of symbols, to be interpreted as a true account of some enterprise.

Machine-updatable, so a database is also a collection of variable.

They are very important, we use them all the time.

Example: (look at your e-grades, sign up for courses) Web indexes, library, catalogues, medical records, bank accounts, stock control, personnel systems, product catalogues, telephone directories, train timetables, airline bookings, credit card details, student records, customer, histories, stock market prices, discussion boards, and so on...

Database Management System(DBMS)#

A piece of software for managing databases and providing access to them.

A DBMS responds to instructions given by application programs, executing on behalf of users.

These instructions are written in the database language of the DBMS. For us , this will be SQL(most popular, with Python, R as top three languages in data science).

Responses include results of queries and a lot of others(obtaining, deleting from the database, and so on)

Relational Database#

A relational database stores and manages structured data.

A database of structured data organised into a collection of relations. — At this moment we only concern with structured data.

Relational databases provide an environment from which data can be accessed or reassembled in a variety of different ways without needing to reorganize the database tables. Each table has a unique identifier, or primary key, which identifies the information in the table, and each row contains a unique instance of data for the categories defined by the columns.

The logical connection between different tables can then be established with the use of foreign keys - a field in a table that connects to the primary key data of another table.

Relational Database Management System(RDBMS)#

Structured data, such as bank statements and electronic address books, is stored in a relational database.

In order to manage all this structured data a relational database management system (RDBMS) is used to create, maintain, access and manipulate the data.

  • A collection of files that store the data
  • A big C program written by someone else that accesses and updates those files for you

A DBMS needs not to be relational, and we only concern with RDBMS.

  • Database Management System = DBMS; Relational DBMS = RDBMS
Edit this page